<?php
include('../includes/dbcon.php') ;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Validate CSRF token
    if (!validate_csrf_token($_POST['csrf_token'])) {
        $_SESSION['message'] = "Invalid CSRF token";
        $_SESSION['message_type'] = "danger";
        redirect('excel_import.php');
    }
    
    try {
        $criteria = json_decode($_POST['criteria'], true);
        $excel_data = json_decode($_POST['excel_data'], true);
        
        if (!$criteria || !$excel_data) {
            throw new Exception("Invalid data received");
        }
        
        $klass = sanitize_input($criteria['klass']);
        $acyear = sanitize_input($criteria['acyear']);
        $term = sanitize_input($criteria['term']);
        $subject = sanitize_input($criteria['subject']);
        $subject_id = intval($criteria['subject_id']);
        $level = sanitize_input($criteria['level']);
        $admin = $_SESSION['username'] ?? 'SYSTEM';
        
        $success_count = 0;
        $error_count = 0;
        $errors = [];
        
        // Prepare the insert statement
        $stmt = $DBcon->prepare("
            INSERT INTO marks (acyear, term, klass, subject, subject_id, regno, level, test, exam, admin) 
            VALUES (:acyear, :term, :klass, :subject, :subject_id, :regno, :level, :test, :exam, :admin)
        ");
        
        foreach ($excel_data as $row) {
            try {
                $regno = sanitize_input($row['regno']);
                $test = floatval($row['test']);
                $exam = floatval($row['exam']);
                
                // Validate scores
                if ($test < 0 || $test > 100 || $exam < 0 || $exam > 100) {
                    throw new Exception("Invalid scores for $regno");
                }
                
                $stmt->bindParam(':acyear', $acyear);
                $stmt->bindParam(':term', $term);
                $stmt->bindParam(':klass', $klass);
                $stmt->bindParam(':subject', $subject);
                $stmt->bindParam(':subject_id', $subject_id);
                $stmt->bindParam(':regno', $regno);
                $stmt->bindParam(':level', $level);
                $stmt->bindParam(':test', $test);
                $stmt->bindParam(':exam', $exam);
                $stmt->bindParam(':admin', $admin);
                
                if ($stmt->execute()) {
                    $success_count++;
                } else {
                    throw new Exception("Database error for $regno");
                }
                
            } catch (Exception $e) {
                $error_count++;
                $errors[] = $e->getMessage();
            }
        }
        
        if ($success_count > 0) {
            $_SESSION['message'] = "Successfully imported $success_count records!";
            if ($error_count > 0) {
                $_SESSION['message'] .= " $error_count records failed to import.";
            }
            $_SESSION['message_type'] = "success";
        } else {
            $_SESSION['message'] = "No records were imported. Errors: " . implode(', ', $errors);
            $_SESSION['message_type'] = "danger";
        }
        
    } catch (Exception $e) {
        $_SESSION['message'] = "Import failed: " . $e->getMessage();
        $_SESSION['message_type'] = "danger";
    }
    
    redirect('excel_import.php');
} else {
    http_response_code(405);
    $_SESSION['message'] = "Method not allowed";
    $_SESSION['message_type'] = "danger";
    redirect('excel_import.php');
}
?>